package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.List;

import model.beans.Topic;


	/**
	 * 
	 * @author 胡伟
	 *  模型层
	 *	提供与数据库交互的JDBC方法
	 */
	public class DAO {
		
		/**
		 * @version 将话题存入数据库中  **受话题属性数的影响 **
		 * @author flyingocean.huwei
		 * @param t 想要存入的话题,使用前要保证t不为空
		 * @throws SQLException 
		 */
		
		public static void addTopic(Topic t) throws SQLException {
			Connection con = DBUtil.getConnection();
			String sql = "INSERT INTO topic(qq, title,content,date,pwd,id) "
		                +" VALUES(?,?,?,?,?,?)";
		        //预编译
		        PreparedStatement ptmt = con.prepareStatement(sql); //预编译SQL，减少sql执行
		        
				ptmt.setString(1,t.getQq());
				ptmt.setString(2,t.getTitle());
				ptmt.setString(3,t.getContent());
				ptmt.setString(4,t.getDate());
				ptmt.setString(5, t.getPwd());
				ptmt.setString(6,t.getTopicId());
				
				ptmt.execute();
				ptmt.close();
		}
		
		
		/**
		 * @author flyingocean.huwei
		 * @version 获得数据库中所有话题
		 * @throws SQLException
		 * @return  List<Topic> Arraylist<Topic>
		 */
		public static List<Topic> queryAllTopic() throws SQLException {
			Connection con = DBUtil.getConnection();
			
			 //预编译
			String sql = "SELECT * FROM topic";
		    PreparedStatement ptmt = con.prepareStatement(sql); //预编译SQL，减少sql执行
		    
		    List<Topic> list = new ArrayList<Topic>();//创建返回集
		    
		    ResultSet rs = ptmt.executeQuery();
				
			while(rs.next()) {	
				Topic t = new Topic();
				t.setQq(rs.getString(1));
				t.setTitle(rs.getString(2));
				t.setContent(rs.getString(3));
				t.setDate(rs.getString(4));
				t.setPwd(rs.getString(5));
				t.setTopicId(rs.getString(6));
				list.add(t);
			}
			//释放资源
			ptmt.close();
			rs.close();
			
			return list;
		}
		
		/**
		 * @author flyingocean.huwei
		 * @version 删除指定id的话题  **要提前保证话题存在 **
		 * @throws SQLException
		 * @param id 指定的要删除话题的id
		 */
		public static void deleteTopicById(String id) throws SQLException {
			Connection con = DBUtil.getConnection();
			
			 //预编译
			String sql = "DELETE FROM topic WHERE id=?";
		    PreparedStatement ptmt = con.prepareStatement(sql); //预编译SQL，减少sql执行
		    ptmt.setString(1,id);
		    ptmt.execute();
			//释放资源
			ptmt.close();
		}
		
		/**
		 * @author flyingocean.huwei
		 * @version 根据qq号查询话题 **受话题属性数影响**
		 * @throws SQLException
		 * @return List<Topic> Arraylist<Topic>
		 */
		public static List<Topic> queryTopicByQQ(String qq) throws SQLException {
			Connection con = DBUtil.getConnection();
			
			 //预编译
			String sql = "SELECT * FROM topic WHERE qq = ?";
		    PreparedStatement ptmt = con.prepareStatement(sql); //预编译SQL，减少sql执行
		    ptmt.setString(1,qq);
		    List<Topic> list = new ArrayList<Topic>();//创建返回集
		    
		    ResultSet rs = ptmt.executeQuery();
				
			while(rs.next()) {	
				Topic t = new Topic();
				t.setQq(rs.getString(1));
				t.setTitle(rs.getString(2));
				t.setContent(rs.getString(3));
				t.setDate(rs.getString(4));
				t.setPwd(rs.getString(5));
				t.setTopicId(rs.getString(6));
				list.add(t);
			}
			//释放资源
			ptmt.close();
			rs.close();
			
			return list;
		}
		
		/**
		 * @author flyingocean.huwei
		 * @param 所要查询的id **受话题属性数影响**
		 * @version 根据id查询话题
		 * @throws SQLException
		 * @return 如果查询成功返回topic对象,否则返回null
		 */
		public static Topic queryTopicByTopicId(String TopicId) throws SQLException {
			Connection con = DBUtil.getConnection();
			
			 //预编译
			String sql = "SELECT * FROM topic WHERE id = ?";
		    PreparedStatement ptmt = con.prepareStatement(sql); //预编译SQL，减少sql执行
		    ptmt.setString(1,TopicId);
		    
		    ResultSet rs = ptmt.executeQuery();
						
		    Topic t = new Topic();
		    
			while(rs.next()) {	
				t.setQq(rs.getString(1));
				t.setTitle(rs.getString(2));
				t.setContent(rs.getString(3));
				t.setDate(rs.getString(4));
				t.setPwd(rs.getString(5));
				t.setTopicId(rs.getString(6));
			}
			if(t.getQq()==null) {	//如果查询不到
				rs.close();
				ptmt.close();
				return null;
			}else {	//如果能查询得到记录
				rs.close();
				ptmt.close();
				return t;
			}
		}
}
